/**
 * Book数据模型模块
 * model\sqlite\book.js
 */
 
//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");
 
/**
 * BookDB构造函数
 * @constructor
 */
function BookDB() {
  this.dbFile = config.dbFile;
  this.instance = null; //BookDB数据库实例
  this.db = null; //SQLite3实例
}
 
/**
 * 创建Book对象
 */
BookDB.getInstance = function () {
  if (!this.instance) {
    this.instance = new BookDB();
  }
  return this.instance;
};
 
/**
 * 连接数据库
 * @returns {Promise}
 */
BookDB.prototype.connect = function () {
  return new Promise((resolve, reject) => {
    this.db = new sqlite3.Database(this.dbFile, (err) => {
      if (err) {
        reject(err);
      } else {
        resolve("connect ok.");
      }
    });
  });
};
 
/**
 * 关闭数据库
 * @returns  {Promise}
 */
BookDB.prototype.close = function () {
  return new Promise((resolve, reject) => {
    this.db.close((err) => {
      if (err) {
        reject(err);
      } else {
        resolve("close ok.");
      }
    });
  });
};
 
/**
 * 获取指定ID的书籍信息
 * @param {Number} bookId 书籍ID
 * @returns  {Promise}
 */
BookDB.prototype.find = function (bookId) {
  return new Promise((resolve, reject) => {
    let sql = `SELECT * FROM books WHERE id=?`;
    let params = [bookId];
    this.db.get(sql, params, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
 
/**
 * 获取书籍信息列表
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
BookDB.prototype.findAll = function (limit = -1, offset = -1,orderBy="id",sort="desc") {
  return new Promise((resolve, reject) => {
    let sql = "";
    let params = [];
    if (limit === -1) {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort}`;
    } else if (offset === -1) {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort} LIMIT ?`;
      params[0] = limit;
    } else {
      sql = `SELECT * FROM books ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
      params[0] = limit;
      params[1] = offset;
    }
    this.db.all(sql, params, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};
 
/**
 * 新增书籍
 * @param {Object} book 书籍数据
 * @returns  {Promise}
 */
BookDB.prototype.add = function (book) {
  return new Promise((resolve, reject) => {
    let sql = `INSERT INTO books (
        title, pic, local_pic, author, publisher, producer,
        subtitle, originalTitle, translator, pubdate, pages, price, 
        binding, series, isbn, intro, doubanId, created_time, 
        updated_time
    ) VALUES (
        ?, ?, ?, ?, ?, ?,
        ?, ?, ?, ?, ?, ?,
        ?, ?, ?, ?, ?, ?,
        ?
    );`;
    let params = [
      book.title,
      book.pic,
      book.local_pic,
      book.author,
      book.publisher,
      book.producer,
      book.subtitle,
      book.originalTitle,
      book.translator,
      book.pubdate,
      book.pages,
      book.price,
      book.binding,
      book.series,
      book.isbn,
      book.intro,
      book.doubanId,
      book.createdTime,
      book.updatedTime,
    ];
 
    this.db.run(sql, params, function(err, result){
      if (err) {
        reject(err);
      } else {
        resolve(this.lastID); //插入的数据的自增ID
      }
    });
  });
};
 
/**
 * 修改书籍
 * @param {Object} book 书籍数据
 * @returns  {Promise}
 */
BookDB.prototype.update = function (book) {
  return new Promise((resolve, reject) => {
    let sql = `UPDATE books SET
 title = ?, pic = ?, local_pic = ?, author = ?, 
 publisher = ?, producer = ?, subtitle = ?, originalTitle = ?, 
 translator = ?, pubdate = ?, pages = ?, price = ?, 
 binding = ?, series = ?, isbn = ?, intro = ?, 
 doubanId = ?, updated_time = ?
 WHERE id = ?
 ;`;
    let params = [
      book.title,
      book.pic,
      book.local_pic,
      book.author,
      book.publisher,
      book.producer,
      book.subtitle,
      book.originalTitle,
      book.translator,
      book.pubdate,
      book.pages,
      book.price,
      book.binding,
      book.series,
      book.isbn,
      book.intro,
      book.doubanId,
      book.updatedTime,
      book.id,
    ];
 
    this.db.run(sql, params, function(err, result){
      if (err) {
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};
 
/**
 * 删除书籍
 * @param {Number} bookId 书籍ID
 * @returns  {Promise}
 */
BookDB.prototype.remove = function (bookId) {
  return new Promise((resolve, reject) => {
    let sql = "DELETE FROM books WHERE id = ?";
    let params = [bookId];
    this.db.run(sql, params, function(err, result){
      if (err) {
        reject(err);
      } else {
        resolve(this.changes); //影响的记录数
      }
    });
  });
};

/**
 * 获取书籍总数
 * @returns  {Promise}
 */
BookDB.prototype.getCount=function(){
    return new Promise((resolve, reject) => {
           let sql = "SELECT count(*) AS total FROM books";
           let params = [];
           this.db.get(sql, params, function(err, result){
             if (err) {
               reject(err);
             } else {
               resolve(result); //影响的记录数
             }
           });
         });
}

/**
 * 获取指定关键字的书籍信息列表
 * @param {String} q 关键字
 * @param {Number} limit 数量
 * @param {Number} offset 开始
 * @returns  {Promise}
 */
BookDB.prototype.search = function (q,limit = -1, offset = -1) {
     return new Promise((resolve, reject) => {
       let sql = "";
       q=`%${q}%`;
       let params = [q,q];
       if (limit === -1) {
         sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ?";
       } else if (offset === -1) {
         sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ?";
         params.push(limit);
       } else {
         sql = "SELECT * FROM books WHERE title LIKE ? OR author LIKE ? LIMIT ? OFFSET ?";
         params.push(limit);
         params.push(offset);
       }
       this.db.all(sql, params, (err, result) => {
         if (err) {
           reject(err);
         } else {
           resolve(result);
         }
       });
     });
};

/**
 * 获取指定ISBN的书籍信息
 * @param {Number} bookId 书籍ISBN
 * @returns  {Promise}
 */
BookDB.prototype.findByIsbn = function (bookIsbn) {
     return new Promise((resolve, reject) => {
       let sql = "SELECT * FROM books WHERE isbn = ?";
       let params = [bookIsbn];
       this.db.get(sql, params, (err, result) => {
         if (err) {
           reject(err);
         } else {
           resolve(result);
         }
       });
     });
};
 
module.exports = BookDB;